#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
竞赛数据迁移脚本：将JSON竞赛数据迁移到SQLite数据库
"""

import json
import os
from pathlib import Path
import sys

# 修复导入路径，确保可以正确导入database.py
current_dir = os.path.dirname(os.path.abspath(__file__))
parent_dir = os.path.abspath(os.path.join(current_dir, '..'))
if parent_dir not in sys.path:
    sys.path.insert(0, parent_dir)

from database import db_manager, competition_db

def migrate_competitions():
    """迁移竞赛数据"""
    print("=" * 60)
    print("开始迁移竞赛数据：JSON -> SQLite")
    print("=" * 60)
    
    competitions_file = 'data/backup/competitions.json'
    if not os.path.exists(competitions_file):
        print("❌ 竞赛数据文件不存在: data/backup/competitions.json")
        return
    
    try:
        with open(competitions_file, 'r', encoding='utf-8') as f:
            competitions_data = json.load(f)
        
        print(f"发现 {len(competitions_data)} 个竞赛数据")
        
        migrated_count = 0
        with db_manager.get_connection() as conn:
            cursor = conn.cursor()
            
            for competition_data in competitions_data:
                try:
                    # 检查竞赛是否已存在
                    cursor.execute('SELECT id FROM competitions WHERE id = ?', (competition_data['id'],))
                    if cursor.fetchone():
                        print(f"竞赛 {competition_data['id']} 已存在，跳过")
                        continue
                    
                    # 插入竞赛数据
                    cursor.execute('''
                        INSERT INTO competitions (
                            id, name, description, status, categories, start_date, end_date,
                            rating, organizer, max_team_size, url, image_url, detail
                        ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
                    ''', (
                        competition_data['id'],
                        competition_data['name'],
                        competition_data.get('description', ''),
                        competition_data.get('status', 'upcoming'),
                        json.dumps(competition_data.get('categories', [])),
                        competition_data.get('start_date', ''),
                        competition_data.get('end_date', ''),
                        competition_data.get('rating', ''),
                        competition_data.get('organizer', ''),
                        competition_data.get('max_team_size', 3),
                        competition_data.get('url', ''),
                        competition_data.get('image_url', ''),
                        competition_data.get('detail', '')
                    ))
                    
                    # 插入分类数据
                    categories = competition_data.get('categories', [])
                    for category in categories:
                        cursor.execute('''
                            INSERT INTO competition_categories (competition_id, category)
                            VALUES (?, ?)
                        ''', (competition_data['id'], category))
                    
                    migrated_count += 1
                    print(f"✅ 成功迁移竞赛: {competition_data['id']} - {competition_data['name']}")
                    
                except Exception as e:
                    print(f"❌ 迁移竞赛 {competition_data.get('id', 'unknown')} 时出错: {e}")
            
            conn.commit()
        
        print(f"\n竞赛数据迁移完成，共迁移 {migrated_count} 个竞赛")
        
        # 更新竞赛状态
        print("正在更新竞赛状态...")
        competition_db.update_competition_status()
        print("竞赛状态更新完成")
        
    except Exception as e:
        print(f"❌ 迁移竞赛数据时出错: {e}")

def verify_migration():
    """验证迁移结果"""
    print("\n" + "=" * 60)
    print("验证迁移结果")
    print("=" * 60)
    
    try:
        # 获取所有竞赛
        competitions = competition_db.get_all_competitions()
        print(f"数据库中的竞赛数量: {len(competitions)}")
        
        # 统计各状态竞赛数量
        status_count = {}
        for comp in competitions:
            status = comp['status']
            status_count[status] = status_count.get(status, 0) + 1
        
        print("各状态竞赛数量:")
        for status, count in status_count.items():
            print(f"  - {status}: {count} 个")
        
        # 获取所有分类
        categories = competition_db.get_all_categories()
        print(f"\n竞赛分类数量: {len(categories)}")
        print("分类列表:")
        for category in categories:
            print(f"  - {category}")
        
        # 测试搜索功能
        print("\n测试搜索功能:")
        
        # 搜索数学相关竞赛
        math_comps = competition_db.search_competitions(query='数学')
        print(f"  - 数学相关竞赛: {len(math_comps)} 个")
        
        # 搜索即将开始的竞赛
        upcoming_comps = competition_db.get_upcoming_competitions()
        print(f"  - 即将开始的竞赛: {len(upcoming_comps)} 个")
        
        # 搜索A级竞赛
        a_rated_comps = competition_db.get_competitions_by_rating('A')
        print(f"  - A级竞赛: {len(a_rated_comps)} 个")
        
        print("\n✅ 迁移验证完成")
        
    except Exception as e:
        print(f"❌ 验证迁移结果时出错: {e}")

def backup_json_file():
    """备份原始JSON文件"""
    print("备份原始JSON文件...")
    
    backup_dir = 'data/backup'
    os.makedirs(backup_dir, exist_ok=True)
    
    source_file = 'data/competitions.json'
    if os.path.exists(source_file):
        backup_path = os.path.join(backup_dir, 'competitions.json')
        try:
            import shutil
            shutil.copy2(source_file, backup_path)
            print(f"✅ 已备份: {source_file} -> {backup_path}")
        except Exception as e:
            print(f"❌ 备份失败: {e}")

def main():
    """主迁移函数"""
    print("竞赛数据迁移工具")
    print("=" * 60)
    
    # backup_json_file()
    # print("原始JSON文件已备份到 data/backup/ 目录")


    # 迁移竞赛数据
    migrate_competitions()
    
    # 验证迁移结果
    verify_migration()
    
    print("\n" + "=" * 60)
    print("竞赛数据迁移完成！")
    print("新的竞赛数据已存储在SQLite数据库中")
    print("=" * 60)

if __name__ == '__main__':
    main()
